<?php
// +----------------------------------------------------------------------
// | Description: 客户
// +----------------------------------------------------------------------
// | Author:  Zl
// +----------------------------------------------------------------------
namespace app\crm\model;

use app\admin\model\User;
use app\common\traits\ActionRecordTrait;
use think\facade\Db;
use app\admin\model\Common;
use think\response\Json;
use think\Validate;
use app\crm\logic\WhereLogic;

class ProductAnalysisModel extends Common
{
    use ActionRecordTrait;
    /**
     * 为了数据库的整洁，同时又不影响Model和Controller的名称
     * 我们约定每个模块的数据表都加上相同的前缀，比如CRM模块用crm作为数据表前缀
     */
    protected $name = 'customer';


    // public function getProductList(array $request): array
    // {
	// 	//echo '欢迎进入品项分析数据报表页面';


	// 	$shopIds = $request['shopIds'] ?? [];

	// 	$where = [];

		// if(!empty($shopIds)){

		//     array_push($where,['shop_id','in',$shopIds]);

		// }
	// 	$start_time = $request['start_time'];
	// 	$end_time = $request['end_time'];
	// 	// $start_time ='1669827661';//2022-12-01
	// 	// $end_time ='1672502399';//2022-12-31
		// array_push($where,['type','=',1]);
		// array_push($where,['shop_type','<>',1]);
		// array_push($where,['shop_id','<>',74076159037951]);
		// array_push($where,['status','=',1]);

	// 	$shopList = Db::name('shop')->where($where)->field('shop_id,shop_name,market_id,market_name,shop_type')->order('market_id')->select()->toArray();

	// 	foreach ($shopList as $k => $v) {

	// 		$dataList = $this->getShopProdut($v['shop_id'],$start_time,$end_time);

	// 		$list[$k]['check_time'] =date('Y-m-01').' -- '.date('Y-m-d');
	// 		$list[$k]['market_name'] =$v['market_name'];
	// 		$list[$k]['shop_name'] =$v['shop_name'];
	// 		$list[$k]['shopId'] =$v['shop_id'];
	// 		$list[$k]['product_type'] ='产品' ;
	// 		$list[$k]['product'] ='' ;
	// 		$list[$k]['personNum'] =$dataList['personNum']?? "0";
	// 		$list[$k]['doubleNum'] =$dataList['numtotal']?? "0";
	// 		$list[$k]['productAmount'] =$dataList['amount']?? "0";


	// 	}

	// 	$data              = [];
	// 	$data['list']      = $list ?? [];


	// 	return $data;
	// }
	public function getProductList(array $request): array{
		$shopIds = $request['shopIds'] ?? [];
		$page = $request['page'] ?? 1;
		$pageSize = $request['page_size'] ?? 10;
		if(empty($request['goods_type']))
		{
			$arr[0] = [
				"number"=>0,
				"repurchase_num"=>0,
				"total_amount"=>0,
				"shop_name"=>"",
				"market_name"=>"汇总",
				"goods_type"=>"",
				"goods_id"=>0
			];
			return $arr;
		}
		if(empty($shopIds)){
			$where = [];
			array_push($where,['type','=',1]);
			array_push($where,['shop_type','<>',1]);
			array_push($where,['shop_id','<>',74076159037951]);
			array_push($where,['status','=',1]);
		    $shopList = Db::name('shop')->where($where)->field('shop_id')->order('market_id')->select()->toArray();
			$shopIds = array_column($shopList,'shop_id');

		}
		$start_time = strtotime($request['start_time']);
		$end_time = strtotime($request['end_time']);
		$where = [];
		array_push($where,['a.deal_time','>=',$start_time]);
		array_push($where,['a.deal_time','<=',$end_time]);
		if(!empty($request['goods_name']))
		{
			array_push($where,['c.name','=',$request['goods_name']]);
		}
		if(!empty($request['shop_name']))
		{
			array_push($where,['b.shop_name','like','%'.$request['shop_name'].'%']);
		}
        if(!empty($type))
        {
            array_push($where,['b.type','=',$type]);
        }


		if($request['type'] == 1)
		{
			array_push($where,['c.goods_type','<>',0]);
			array_push($where,['a.goods_id','<>',0]);
			if(!empty($request['shop_id']))
			{
				array_push($where,['a.shop_id','in',$request['shop_id']]);
			}
			$data = Db::name('shop_goods_analyse')
			->alias('a')
			->field('a.shop_id as shop_id,SUM(a.number) as number,SUM(a.repurchase_num) as repurchase_num,SUM(a.total_amount) as total_amount,b.shop_name,b.market_name,c.goods_type,c.name as goods_name,a.goods_id')
			->join('shop b','a.shop_id = b.shop_id','LEFT')
			->join('shop_goods c','a.goods_id = c.id','LEFT')
			->where($where)
			->group('c.goods_type,c.name,b.shop_name,b.market_name,a.goods_id')
			// ->limit(($page-1) * $pageSize, $pageSize)
			->select()->toArray();
			$arr = [
				"number"=>0,
				"repurchase_num"=>0,
				"total_amount"=>0,
				"shop_name"=>"",
				"market_name"=>"汇总",
				"goods_type"=>"",
				"goods_name"=>"",
				"goods_id"=>0
			];
			foreach($data as $value)
			{
				$arr['number'] += $value['number'];
				$arr['repurchase_num'] += $value['repurchase_num'];
				$arr['total_amount'] += $value['total_amount'];
			}
			array_push($data,$arr);
		}else{
			array_push($where,['a.goods_type','in',$request['goods_type']]);
			$data = Db::name('shop_goods_analyse')
			->alias('a')
			->field('SUM(a.number) as number,SUM(a.repurchase_num) as repurchase_num,SUM(a.total_amount) as total_amount,b.shop_name,b.market_name,a.goods_type,b.shop_id')
			->join('shop b','a.shop_id = b.shop_id','LEFT')
			->join('shop_goods c','a.goods_id = c.id','LEFT')
			->where($where)
			->group('a.goods_type,b.shop_id,b.shop_name,b.market_name')
			// ->limit(($page-1) * $pageSize, $pageSize)
			->select()->toArray();
			$arr = [
				"number"=>0,
				"repurchase_num"=>0,
				"total_amount"=>0,
				"shop_name"=>"",
				"market_name"=>"汇总",
				"goods_type"=>"",
				"goods_id"=>0
			];
			foreach($data as $value)
			{
				$arr['number'] += $value['number'];
				$arr['repurchase_num'] += $value['repurchase_num'];
				$arr['total_amount'] += $value['total_amount'];
			}
			array_push($data,$arr);
		}
        if(!empty($request['export']))
        {
            $options = [
                1=>'补水保湿',
                2=>'祛黄提亮',
                3=>'控油祛痘',
                4=>'安舒修复',
                5=>'面部仪器',
                6=>'面部手法',
                7=>'身体仪器',
                8=>'身体手法',
            ];

            foreach ($data as $key => $value)
            {
                if($value['goods_type']=="")
                    continue;
                $data[$key]['goods_type']=$options[$value['goods_type']];
                $data[$key]['date']=date('Y-m-d',$start_time).'--'.date('Y-m-d',$end_time);
            }
        }

		return ['msg'=>"记录已获取",'data'=>$data];
	}

	public function getShopProdut($shopId,$start_time = '',$end_time = ''){

		$where = [];
		array_push($where,['goods_type','>=',1]);

		$goodTypeList = Db::name('shop_goods')->where($where)->field('id,goods_type')->select()->toArray();

		$goodIds = array_values(array_unique(array_column($goodTypeList,'id')));

		$where = [];
		array_push($where,['deal_time','>=',$start_time]);
		array_push($where,['deal_time','<=',$end_time]);
		array_push($where,['shop_id','=',$shopId]);
		array_push($where,['goods_id','in',$goodIds]);
		array_push($where,['status','=',1]);

		$goodListAmount = Db::name('shop_goods_analyse')->where($where)->column('sum(pay_price * number) as zAmount,shop_id');

		$goodListNum = Db::name('shop_goods_analyse')->where($where)->column('sum(number) as zNum,shop_id');

		$goodListPerson = Db::name('shop_goods_analyse')->distinct(true)->where($where)->field('uid')->select()->toArray();


		$data['amount'] =$goodListAmount[0]['zAmount'];//销售总金额
		$data['numtotal'] =$goodListNum[0]['zNum']; // 复购数量
		$data['personNum'] =count($goodListPerson); // 购买人数

		return $data;
	}

	/*
	* @ 门店产品明细数据报表计算方法
	* @ 时间 2022-12-28
	*/
	public function getShopProductList($shopId,$request){

		$start_time='1669827661';
		$end_time ='1672502399';
		$shopList = Db::name('shop')->where('shop_id','=',$shopId)->field('shop_name,market_name')->select()->toArray();

		$where = [];
		array_push($where,['goods_type','>=',1]);
		$goodTypeList = Db::name('shop_goods')->where($where)->field('id,goods_type,name')->order("goods_type asc")->select()->toArray();


		//产品分类数组
		$prodectArray[1]='补水保湿';
		$prodectArray[2]='祛黄提亮';
		$prodectArray[3]='控油祛痘';
		$prodectArray[4]='安舒修复';
		$prodectArray[5]='面部仪器';
		$prodectArray[6]='面部手法';
		$prodectArray[7]='身体仪器';
		$prodectArray[8]='身体手法';



		foreach ($goodTypeList as $k => $v) {

			$productAmount = $this->setPersonShopList($shopId,$v['id'],$start_time,$end_time);


			$list[$k]['poductName'] = $v['name'];  //产品名称
			$list[$k]['poductType'] = $prodectArray[$v['goods_type']];  //产品分类
			$list[$k]['shopName'] = $shopList[0]['shop_name'];  //店面名称
			$list[$k]['marketName'] = $shopList[0]['market_name'];  //市场名称

			$list[$k]['buyerNum'] = $productAmount['personNum']?? "0";  //购买人数
			$list[$k]['repurchaseNum'] = $productAmount['numtotal']?? "0"; //复购次数
			$list[$k]['salesAmount'] = $productAmount['amount']?? "0";  //成交金额

		}

		/* echo '<pre>';
		print_r($list);

		exit; */

		$data              = [];
		$data['list']      = $list ?? [];


		return $data;
	}

	//单独一个门店的项目数据
	public function setPersonShopList($shopId,$good_id,$start_time = '',$end_time = ''){

		$where = [];
		array_push($where,['deal_time','>=',$start_time]);
		array_push($where,['deal_time','<=',$end_time]);
		array_push($where,['shop_id','=',$shopId]);
		array_push($where,['goods_id','=',$good_id]);
		array_push($where,['status','=',1]);

		$goodListAmount = Db::name('shop_goods_analyse')->where($where)->column('sum(pay_price * number) as zAmount,shop_id');

		$goodListNum = Db::name('shop_goods_analyse')->where($where)->column('sum(number) as zNum,shop_id');

		$goodListPerson = Db::name('shop_goods_analyse')->distinct(true)->where($where)->field('uid')->select()->toArray();


		$data['amount'] =$goodListAmount[0]['zAmount'];//销售总金额
		$data['numtotal'] =$goodListNum[0]['zNum']; // 复购数量
		$data['personNum'] =count($goodListPerson); // 购买人数

		return $data;
	}

	/*
	@ *** 销售出库单的产品明细新增单独数据表 --2022-12-16
	*/
	public function addProductlist(array $orderGoodsList=[],$shop_id,$uid,$deal_time){

		$goodList = Db::name('shop_goods')->column('goods_type','id');;

		if(!empty($orderGoodsList)){

			foreach ($orderGoodsList as $k => $v) {

				$productGoodsList[$k]['uid'] = $uid;
				$productGoodsList[$k]['order_id'] = $v['order_id'];
				$productGoodsList[$k]['shop_id'] = $shop_id;
				$productGoodsList[$k]['name'] = $v['goods_name'];
				$productGoodsList[$k]['goods_id'] = $v['goods_id'];
				$productGoodsList[$k]['category_id'] = $goodList[$v['goods_id']];
				$productGoodsList[$k]['goods_price'] = $v['goods_price'];
				$productGoodsList[$k]['pay_price'] = $v['pay_price'];
				$productGoodsList[$k]['number'] = $v['number'];
				$productGoodsList[$k]['deal_time'] = $deal_time;
				$productGoodsList[$k]['create_time'] = time();
				$productGoodsList[$k]['status'] = 1;

			}

			Db::name('shop_goods_analyse')->insertAll($productGoodsList);
		}

	}
	// 作废产品  根据销售出库单的作废状态
	public function checkProductList($id){

		if($id){

			$goodList = Db::name('shop_goods_analyse')->where('order_id',$id)->select()->toArray();

			foreach ($goodList as $k => $v){

				$data['status'] = 2; // 产品状态： 2是作废

				$bool = Db::name('shop_goods_analyse')->where('id','=',$v['id'])->update($data);

			}

		}
	}
	//处理品相信息
	public function setOrderGoodsAnalyse()
	{
		$extra = [170];

		$start_time = strtotime(date('Y-m-d',strtotime('-1 day')));
		$end_time = $start_time+24*60*60-1;

		Db::startTrans();
		try{
			$where = [];
			array_push($where,['b.status','=',2]);
			array_push($where,['a.goods_id','not in',$extra]);
			array_push($where,['b.deal_time','>=',$start_time]);
			array_push($where,['b.deal_time','<=',$end_time]);

			$res = Db::name('shop_order_goods')
				->field('b.shop_id,b.uid,a.goods_id,a.number,a.pay_price')
				->alias('a')
				->join('shop_order b','a.order_id = b.id','LEFT')
				// ->join('shop_goods c','a.goods_id = c.id')
				// ->join('shop d','a.shop_id = d.shop_id')
				->where($where)
				// ->group('b.shop_id,a.goods_id')
				->select()->toArray();
			$firstData = [];
			foreach($res as $value)
			{
				$arr = [];
				if(empty($firstData[$value['shop_id']][$value['goods_id']]))
				{
					$firstData[$value['shop_id']][$value['goods_id']]['number'] = 1;
					$firstData[$value['shop_id']][$value['goods_id']]['uid'] = [];
					array_push($firstData[$value['shop_id']][$value['goods_id']]['uid'],$value['uid']);
					$firstData[$value['shop_id']][$value['goods_id']]['repurchase_num'] = 1;
					$firstData[$value['shop_id']][$value['goods_id']]['total_amount'] = $value['number']*$value['pay_price'];
				}else{
					$firstData[$value['shop_id']][$value['goods_id']]['number'] += 1;
					$firstData[$value['shop_id']][$value['goods_id']]['repurchase_num'] += 1;
					if(!in_array($value['uid'],$firstData[$value['shop_id']][$value['goods_id']]['uid']))
					{
						array_push($firstData[$value['shop_id']][$value['goods_id']]['uid'],$value['uid']);
					}
					$firstData[$value['shop_id']][$value['goods_id']]['total_amount'] += $value['number']*$value['pay_price'];
				}
			}
			$insertData = [];
			foreach($firstData as $key => $value)
				foreach($value as $k => $v)
				{
					$arr = [
						'shop_id' =>$key,
						'goods_id' =>$k,
						'number' => count($v['uid']),
						'repurchase_num' => $v['repurchase_num'],
						'total_amount' =>$v['total_amount'],
						'deal_time'=>$start_time
					];
					array_push($insertData,$arr);
				}
			 $res = Db::name('shop_goods_analyse')->insertAll($insertData);

			$where = [];
			array_push($where,['b.status','=',2]);
			array_push($where,['a.goods_id','not in',$extra]);
			array_push($where,['b.deal_time','>=',$start_time]);
			array_push($where,['b.deal_time','<=',$end_time]);

			$res = Db::name('shop_order_goods')
				->field('b.shop_id,b.uid,c.goods_type,a.number,a.pay_price')
				->alias('a')
				->join('shop_order b','a.order_id = b.id','LEFT')
				->join('shop_goods c','a.goods_id = c.id')
				->where($where)
				->select()->toArray();


			$firstData = [];
			foreach($res as $value)
			{
				$arr = [];
				if(empty($firstData[$value['shop_id']][$value['goods_type']]))
				{
					$firstData[$value['shop_id']][$value['goods_type']]['number'] = 1;
					$firstData[$value['shop_id']][$value['goods_type']]['uid'] = [];
					array_push($firstData[$value['shop_id']][$value['goods_type']]['uid'],$value['uid']);
					$firstData[$value['shop_id']][$value['goods_type']]['repurchase_num'] = 1;
					$firstData[$value['shop_id']][$value['goods_type']]['total_amount'] = $value['number']*$value['pay_price'];
				}else{
					$firstData[$value['shop_id']][$value['goods_type']]['number'] += 1;
					$firstData[$value['shop_id']][$value['goods_type']]['repurchase_num'] += 1;
					if(!in_array($value['uid'],$firstData[$value['shop_id']][$value['goods_type']]['uid']))
					{
						array_push($firstData[$value['shop_id']][$value['goods_type']]['uid'],$value['uid']);
					}
					$firstData[$value['shop_id']][$value['goods_type']]['total_amount'] += $value['number']*$value['pay_price'];
				}
			}
			$insertData = [];
			foreach($firstData as $key => $value)
				foreach($value as $k => $v)
				{
					$arr = [
						'shop_id' =>$key,
						'goods_type' =>$k,
						'number' => count($v['uid']),
						'repurchase_num' => $v['repurchase_num'],
						'total_amount' =>$v['total_amount'],
						'deal_time'=>$start_time
					];
					array_push($insertData,$arr);
				}
			$res = Db::name('shop_goods_analyse')->insertAll($insertData);

			$where = [];

			array_push($where,['status','=',4]);
			array_push($where,['cancel_time','<>',0]);
			array_push($where,['cancel_time','>=',$start_time]);
			array_push($where,['cancel_time','<',$end_time]);

			$res = Db::name('shop_order')->where($where)->select()->toArray();


			foreach($res as $value)
			{
				if(date('Y-m-d',$value['deal_time']) == date('Y-m-d',$value['cancel_time']))
					{
						continue;
					}
				$orderId = $value['id'];
				$where = [];
				array_push($where,['a.order_id','=',$orderId]);

				$orderGoods = Db::name('shop_order_goods')->alias('a')
					->field('a.goods_id,b.goods_type,a.number,a.pay_price')
					->join('shop_goods b','a.goods_id = b.id')
					->where($where)->find();


				$where = [];
				array_push($where,['shop_id','=',$value['shop_id']]);
				array_push($where,['goods_id','=',$orderGoods['goods_id']]);
				array_push($where,['deal_time','=',strtotime(date('Y-m-d',$value['deal_time']))]);
				$orderGoodsAnalyse = Db::name('shop_goods_analyse')->where($where)->find();
				$UpdateorderGoodsAnalyse['repurchase_num']=$orderGoodsAnalyse['repurchase_num']-1;
				$UpdateorderGoodsAnalyse['total_amount']=$orderGoodsAnalyse['total_amount']-$orderGoods['number']*$orderGoods['pay_price'];

				$where = [];
				array_push($where,['b.uid','=',$value['uid']]);
				array_push($where,['b.deal_time','>=',$value['deal_time']]);
				array_push($where,['b.deal_time','<=',$value['deal_time']+24*60*60-1]);
				array_push($where,['b.shop_id','=',$value['shop_id']]);
				array_push($where,['a.goods_id','=',$orderGoods['goods_id']]);
				$orderGoodsData = Db::name('shop_order_goods')->alias('a')
					->field('a.goods_id,count(b.uid) as num')
					->join('shop_order b','a.order_id = b.id')
					->group('b.uid')
					->where($where)->find();
				if($orderGoodsData['num'] == 1)
				{
					$UpdateorderGoodsAnalyse['number']=$orderGoodsAnalyse['number']-1;
				}

				$where = [];
				array_push($where,['id','=',$orderGoodsAnalyse['id']]);
				 $res = Db::name('shop_goods_analyse')->where($where)->update($UpdateorderGoodsAnalyse);

				$where = [];
				array_push($where,['shop_id','=',$value['shop_id']]);
				array_push($where,['goods_id','=',0]);
				array_push($where,['goods_type','=',$orderGoods['goods_type']]);
				array_push($where,['deal_time','=',strtotime(date('Y-m-d',$value['deal_time']))]);
				$orderGoodsAnalyse = Db::name('shop_goods_analyse')->where($where)->find();
				$UpdateorderGoodsAnalyse['repurchase_num']=$orderGoodsAnalyse['repurchase_num']-1;
				$UpdateorderGoodsAnalyse['total_amount']=$orderGoodsAnalyse['total_amount']-$orderGoods['number']*$orderGoods['pay_price'];

				$where = [];
				array_push($where,['b.uid','=',$value['uid']]);
				array_push($where,['b.deal_time','>=',$value['deal_time']]);
				array_push($where,['b.deal_time','<=',$value['deal_time']+24*60*60-1]);
				array_push($where,['b.shop_id','=',$value['shop_id']]);
				array_push($where,['c.goods_type','=',$orderGoods['goods_type']]);
				$orderGoodsData = Db::name('shop_order_goods')->alias('a')
					->field('c.goods_type,count(b.uid) as num')
					->join('shop_order b','a.order_id = b.id')
					->join('shop_goods c','a.goods_id = c.id')
					->group('b.uid')
					->where($where)->find();

				if($orderGoodsData['num'] == 1)
				{
					$UpdateorderGoodsAnalyse['number']=$orderGoodsAnalyse['number']-1;
				}

				$where = [];
				array_push($where,['id','=',$orderGoodsAnalyse['id']]);
				$res = Db::name('shop_goods_analyse')->where($where)->update($UpdateorderGoodsAnalyse);

			}




			Db::commit();
		} catch (\Exception $e) {
			// 回滚事务
			Db::rollback();
			return json($e->getMessage());
		}
	}


	public function getGoodsList()
	{
		$where = [];
		array_push($where,['goods_type','<>',0]);
		$data = Db::name('shop_goods')->field('name')->where($where)->group('name')->select()->toArray();
		return ['msg'=>"记录已获取",'data'=>$data];
	}
}
